CREATE OR REPLACE PACKAGE BODY ZOECOLLECT.ZOEPKG_COLLECT_SCHEDULER
AS
-- 北极熊创建于： 2019.03.15
-- 版权 2019 中国
-- 保留所有权利
--	文件名:
-- 		文件名
--	描述:
-- 		基本说明
--   对象关联:
--      对象关联
--	注意事项:
--		基本注意事项
--	修改 - （年-月-日） - 描述
--
  

    FUNCTION GENERATE_DEPLOYMENT_CODE(iv_job_id IN VARCHAR2)
    RETURN GTV_SQL
    AS
        lv_repeat_interval  VARCHAR2(64);
        lt_clc_metric_dict  ZOECOLLECT.CLC_METRIC_DICT%ROWTYPE;
        lv_sql  GTV_SQL;
        ln_sql_i NUMBER;
	BEGIN
        ln_sql_i := 1;
        
        --  根据调度任务ID获取调度计划，返回任务执行周期
            SELECT REPEAT_INTERVAL INTO lv_repeat_interval FROM ZOECOLLECT.CLC_SCHEDULER_SCHEDULES 
                WHERE SCHEDULE_ID = (SELECT SCHEDULE_ID FROM ZOECOLLECT.CLC_SCHEDULER_JOBS WHERE JOB_ID=iv_job_id);
            lv_sql(ln_sql_i) := lv_repeat_interval;
            
        --  根据调度任务ID获取执行指标，返回指标执行程序
            ln_sql_i := ln_sql_i + 1;
            FOR c_i IN (SELECT collect_config FROM ZOECOLLECT.CLC_METRIC_DICT  
                WHERE METRIC_ID IN (SELECT METRIC_ID FROM ZOECOLLECT.CLC_OBJECT_METRIC_CONFIG a, ZOECOLLECT.CLC_SCHEDULER_JOBS b
                    WHERE b.JOB_ID=iv_job_id and a.OBJECT_ID=b.OBJECT_ID and a.SCHEDULE_ID=b.SCHEDULE_ID))
            LOOP
                lv_sql(ln_sql_i) := json_value(c_i.collect_config,'$.COLLECT_PARAMETER')||chr(10);
                lv_sql(ln_sql_i) := lv_sql(ln_sql_i)||json_value(c_i.collect_config,'$.OWNER')||chr(10);
                lv_sql(ln_sql_i) := lv_sql(ln_sql_i)||json_value(c_i.collect_config,'$.TABLE_NAME');
                ln_sql_i := ln_sql_i + 1;
            END LOOP;
            FOR c_i IN (SELECT PROGRAM_CONFIG FROM ZOECOLLECT.CLC_PROGRAM_DICT
                WHERE PROGRAM_ID IN (SELECT DISTINCT PROGRAM_ID FROM ZOECOLLECT.CLC_METRIC_PROGRAM_CONFIG  
                    WHERE METRIC_ID IN (SELECT METRIC_ID FROM ZOECOLLECT.CLC_OBJECT_METRIC_CONFIG a, ZOECOLLECT.CLC_SCHEDULER_JOBS b
                        WHERE b.JOB_ID=iv_job_id and a.OBJECT_ID=b.OBJECT_ID and a.SCHEDULE_ID=b.SCHEDULE_ID)))
            LOOP
                lv_sql(ln_sql_i) := json_value(c_i.PROGRAM_CONFIG,'$.PROGRAM_NAME')||chr(10);
                lv_sql(ln_sql_i) := lv_sql(ln_sql_i)||json_value(c_i.PROGRAM_CONFIG,'$.OWNER')||chr(10);
                lv_sql(ln_sql_i) := lv_sql(ln_sql_i)||json_value(c_i.PROGRAM_CONFIG,'$.PROGRAM_VERSION');
                ln_sql_i := ln_sql_i + 1;
            END LOOP;
            SELECT TEXT INTO lv_sql(ln_sql_i) FROM ZOECOLLECT.CLC_SOURCE;
            RETURN  lv_sql;
    END GENERATE_DEPLOYMENT_CODE;
    
	PROCEDURE DEPLOYMENT_SCHEDULE_JOB(iv_object_id IN VARCHAR2)
	AS
        lv_db_link  VARCHAR2(128);
        lv_sql      VARCHAR2(4000);
        lv_sql_json VARCHAR2(32767);
        lv_job_name VARCHAR2(128);
        lv_job_type VARCHAR2(128);
        lv_job_action VARCHAR2(400);
        lv_repeat_interval VARCHAR2(128);
	BEGIN
         --  根据DBID获取数据库链路
        lv_db_link := ZOEDEVOPS.ZOEPKG_UTILITY.GET_REMOTE_DB_LINK(iv_object_id);
       --ltv_sql := GENERATE_DEPLOYMENT_CODE(iv_job_id);
        --  推送采集指标程序
            --  推送数据采集表结构
            --ZOEDEVOPS.ZOEPKG_UTILITY.SYNC_TABLE_DEFINE('ZOECOLLECT','CLC_METRIC_DICT','CREATE',lv_db_link);
            --ZOEDEVOPS.ZOEPKG_UTILITY.SYNC_TABLE_DEFINE('ZOECOLLECT','CLC_OBJECT_METRIC_CONFIG','CREATE',lv_db_link);


            --ZOEDEVOPS.ZOEPKG_UTILITY.SYNC_TABLE_DEFINE('ZOECOLLECT','CLC_TREND_SPACE_USED','CREATE',lv_db_link);
            --ZOEDEVOPS.ZOEPKG_UTILITY.SYNC_TABLE_DEFINE('ZOECOLLECT','CLC_STANDARD_METRIC_RECORD','CREATE',lv_db_link);
            --  推送数据采集字典数据
            --ZOEDEVOPS.ZOEPKG_UTILITY.SYNC_DICT_DATA('ZOECOLLECT','CLC_METRIC_DICT','APPEND',lv_db_link);
            --ZOEDEVOPS.ZOEPKG_UTILITY.SYNC_DICT_DATA('ZOECOLLECT','CLC_OBJECT_METRIC_CONFIG','APPEND',lv_db_link);

        --  调度计划任务推送
            --  推送调度任务
            lv_sql := ' DBMS_SCHEDULER.CREATE_JOB (';
            lv_sql := lv_sql||'job_name => '''||lv_job_name||''',';
            lv_sql := lv_sql||'job_type => '''||lv_job_type||''',';
            lv_sql := lv_sql||'job_action => '''||lv_job_action||''',';
            lv_sql := lv_sql||'start_time => '||SYSDATE||',';
            lv_sql := lv_sql||'repeat_interval => '''||lv_repeat_interval||''',';
            lv_sql := lv_sql||'enable => true)';
            lv_sql_json := '{"SQL_TYPE":"DDL",';
            lv_sql_json := lv_sql_json ||'"SQL_TEXT":';
            lv_sql_json := lv_sql_json ||'"'|| lv_sql ||'"}';
            ZOEDEVOPS.ZOEPRC_IF_REMOTE_EXEC_SQL(lv_sql_json,lv_db_link); 

	END	DEPLOYMENT_SCHEDULE_JOB;

    FUNCTION GET_SCHEDULE_STATUS(iv_object_id IN VARCHAR2, in_schedule_id IN NUMBER, in_program_id IN NUMBER)
    RETURN NUMBER
    AS
        lv_sql VARCHAR2(32767);
        lv_sql_json VARCHAR2(32767);
        lv_error_return VARCHAR2(4000);
        lv_error_type ZOEDEVOPS.DVP_ERROR_LOG.ERROR_TYPE%TYPE;
        lv_error_info ZOEDEVOPS.DVP_ERROR_LOG.ERROR_INFO%TYPE;
        lv_db_link  VARCHAR2(128);
        lv_job_name VARCHAR2(128);
        ln_count NUMBER;
    BEGIN
        -- 设置程序名上下文
        ZOEDEVOPS.ZOEPRC_SET_DEVOPS_CONTEXT('CURRENT_PROGRAM','ZOEDEVOPS.XXX');
        lv_db_link := ZOEDEVOPS.ZOEPKG_UTILITY.GET_REMOTE_DB_LINK(iv_object_id);
        SELECT JOB_NAME INTO lv_job_name FROM ZOECOLLECT.CLC_SCHEDULER_JOBS
            WHERE object_id = iv_object_id
                and schedule_id = in_schedule_id
                and program_id = in_program_id;
        lv_sql := 'SELECT COUNT(1) FROM DBA_SCHEDULER_JOBS';
        lv_sql := lv_sql||' WHERE JOB_NAME = '''||lv_job_name||'''';
        lv_sql_json := '{"SQL_TYPE":"DQL",';  
			lv_sql_json := lv_sql_json ||'"SQL_TEXT":';  
			lv_sql_json := lv_sql_json ||'"'|| lv_sql ||'"}';  
			ZOEDEVOPS.ZOEPRC_IF_REMOTE_EXEC_SQL(lv_sql_json,lv_db_link);
        SELECT JSON_VALUE(lv_sql_json,'$.SQL_COUNT') INTO ln_count FROM DUAL;
        RETURN ln_count;
    EXCEPTION
    WHEN OTHERS THEN
        lv_error_type := 'PLSQL';
        lv_error_info := '{"ERROR_TYPE":"'||lv_error_type||'",';
        lv_error_info := lv_error_info || '"ERROR_CONTENT":';
        lv_error_info := lv_error_info || '{"ERROR_PROGRAM":"'||SYS_CONTEXT('ZOE_DEVOPS_CONTEXT','CURRENT_PROGRAM')||'",';
        lv_error_info := lv_error_info || '"ERROR_MESSAGE":"'||SQLERRM||'",';
        lv_error_info := lv_error_info || '"ERROR_SQL":"'||TRUNC(lv_sql,2000)||'"}}';
        lv_error_return := ZOEDEVOPS.ZOEFUN_WERROR_LOG(lv_error_info);
    END;
    
    PROCEDURE PUSH_TABLE_DEFINE(iv_owner IN VARCHAR2, iv_table_name IN VARCHAR2, iv_sync_mode IN VARCHAR2,iv_object_id IN VARCHAR2)
    AS
        lv_db_link  VARCHAR2(128);
    BEGIN
        lv_db_link := ZOEDEVOPS.ZOEPKG_UTILITY.GET_REMOTE_DB_LINK(iv_object_id);
        ZOEDEVOPS.ZOEPKG_UTILITY.SYNC_TABLE_DEFINE(iv_owner,iv_table_name,iv_sync_mode,lv_db_link);
    END PUSH_TABLE_DEFINE;
    
    PROCEDURE TEST_REMOTE_METRIC(iv_object_id IN VARCHAR2, in_metric_id IN NUMBER)
   AS
        lv_db_link  VARCHAR2(128);
        lv_sql      VARCHAR2(4000);
        lv_sql_json VARCHAR2(32767);
   BEGIN
        lv_db_link := ZOEDEVOPS.ZOEPKG_UTILITY.GET_REMOTE_DB_LINK(iv_object_id);
        lv_sql  := 'BEGIN ZOECOLLECT.ZOEPKG_COLLECT_PROGRAM.TEST_METRIC('''||in_metric_id||'''); END;';
        lv_sql_json := '{"SQL_TYPE":"DDL",';
        lv_sql_json := lv_sql_json ||'"SQL_TEXT":';
        lv_sql_json := lv_sql_json ||'"'|| lv_sql ||'"}';
        ZOEDEVOPS.ZOEPRC_IF_REMOTE_EXEC_SQL(lv_sql_json,lv_db_link); 
   END TEST_REMOTE_METRIC;

    PROCEDURE TEST_REMOTE_METRIC_SCHEDULE(iv_object_id IN VARCHAR2, in_schedule_id IN NUMBER)
   AS
        lv_db_link  VARCHAR2(128);
        lv_sql      VARCHAR2(4000);
        lv_sql_json VARCHAR2(32767);
   BEGIN
        lv_db_link := ZOEDEVOPS.ZOEPKG_UTILITY.GET_REMOTE_DB_LINK(iv_object_id);
        lv_sql  := 'BEGIN ZOECOLLECT.ZOEPKG_COLLECT_PROGRAM.COLLECT_PROGRAM('''||iv_object_id||''','''||in_schedule_id||'''); END;';
        lv_sql_json := '{"SQL_TYPE":"DDL",';
        lv_sql_json := lv_sql_json ||'"SQL_TEXT":';
        lv_sql_json := lv_sql_json ||'"'|| lv_sql ||'"}';
        ZOEDEVOPS.ZOEPRC_IF_REMOTE_EXEC_SQL(lv_sql_json,lv_db_link); 
   END TEST_REMOTE_METRIC_SCHEDULE;
   
END ZOEPKG_COLLECT_SCHEDULER;

